﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ADOX;
using System.Data.SqlClient;
using Capstone_project.DatabaseAccessLayer;

namespace Capstone_project
{
    public partial class frmTaskManagement : Form
    {
        public static string SelectedTable = string.Empty;
        ConnectData connData = new ConnectData();
        SqlConnection con = Common.con;
        SqlDataAdapter adap;
        public static string selectedID;
        int num = 0;
        DataTable dt = new DataTable();
        public frmTaskManagement()
        {
            InitializeComponent();
        }

        private void frmTaskManagement_Load(object sender, EventArgs e)
        {
            dt.Rows.Clear();
            
            string sql = "SELECT TASK.ID_TASK, TASK.NAME_TASK, ASSIGN_TASK_STATUS.STATUS, TASK.COMPLETE, TASK_GROUP.NAME_TASK_GROUP, PRIORITY.LEVEL, TASK.TIMES"
            +" FROM PRIORITY INNER JOIN (TASK_GROUP INNER JOIN (ASSIGN_TASK_STATUS INNER JOIN TASK ON ASSIGN_TASK_STATUS.ID_TASK_STATUS = TASK.ID_TASK_STATUS) ON TASK_GROUP.ID_TASK_GROUP = TASK.ID_TASK_GROUP) ON PRIORITY.ID_PRIORITY = TASK.ID_PRIORITY";
            if (con.State != ConnectionState.Open)
                con.Open();
            SqlCommand com = new SqlCommand(sql,con);
            adap = new SqlDataAdapter(com);
            adap.Fill(dt);
            com.ExecuteNonQuery();
            con.Close();
            dataGridView1.DataSource = dt.DefaultView;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i % 2 == 0)
                {
                    dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.LightGray;
                }
                if (dataGridView1.Rows[i].Cells[2].Value.ToString() == "Completed")
                {
                    dataGridView1.Rows[i].Cells[2].Style.BackColor = Color.Green;
                }
                else if (dataGridView1.Rows[i].Cells[2].Value.ToString() == "Improgress")
                {
                    dataGridView1.Rows[i].Cells[2].Style.BackColor = Color.SaddleBrown;
                }
                else if (dataGridView1.Rows[i].Cells[2].Value.ToString() == "New")
                {
                    dataGridView1.Rows[i].Cells[2].Style.BackColor = Color.SteelBlue;
                }
            }
        }

        private void btnAddnewtask_Click(object sender, EventArgs e)
        {
            frmAddTask frmaddtask = new frmAddTask();
            frmaddtask.Show();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            frmTaskManagement_Load(null,null);
        }

        private void btnEdittask_Click(object sender, EventArgs e)
        {
            selectedID= dataGridView1.CurrentRow.Cells[0].Value.ToString();
            frmEditTask frmedittask = new frmEditTask();
            frmedittask.Show();
        }

        private void btnDeleteTask_Click(object sender, EventArgs e)
        {
            string a = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            try
            {
                string sql = "DELETE FROM TASK WHERE ID_TASK='" + a + "'";
                if (con.State != ConnectionState.Open)
                    con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                int kq = com.ExecuteNonQuery();
                con.Close();
                if (kq != 0)
                {
                    MessageBox.Show("Delete task " + a + " successful.");
                    btnRefresh_Click(null, null);
                }
            }
            catch { MessageBox.Show("Fail to delete task "+a); }
        }

        
        #region DEFINE METHODS TO IMPORT TASK FROM EXCEL FILE
        private void btnImporttaskFrExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select file";
            fdlg.InitialDirectory = @"c:\";
            fdlg.FileName = txtFileName.Text;
            fdlg.Filter = "Excel Sheet(*.xls)|*.xls|Excel Workbook(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
            fdlg.FilterIndex = 1;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = fdlg.FileName;
                Import();
                Application.DoEvents();
            }
        }
        private void Import()
        {
            if (txtFileName.Text.Trim() != string.Empty)
            {
                try
                {
                    string[] strTables = GetTableExcel(txtFileName.Text);

                    frmSelectSheetofExcel objSelectsheet = new frmSelectSheetofExcel(strTables);
                    objSelectsheet.ShowDialog(this);
                    objSelectsheet.Dispose();
                    if ((SelectedTable != string.Empty) && (SelectedTable != null))
                    {
                        dt = GetDataTableExcel(txtFileName.Text, SelectedTable);
                        //dataGridView1.DataSource = dt.DefaultView;
                        for (int i = num; i < dt.Rows.Count + num; i++)
                        {
                            object[] row1 = new object[] { null, dt.Rows[i - num][1], dt.Rows[i - num][2], dt.Rows[i - num][3], dt.Rows[i - num][4], dt.Rows[i - num][5], dt.Rows[i - num][6] };
                            dataGridView1.Rows.Add(row1);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    dataGridView1.Refresh();
                }
            }
        }
        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            adapter.Fill(ds);
            return ds.Tables[0];
        }

        public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.Jet.Sql.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count > 0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }
        #endregion
    }
}
